import pymysql
from twisted.enterprise import adbapi  # 异步优化可选

class MySQLPipeline:
    def __init__(self, host, port, db, user, password):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = password

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            host=crawler.settings.get('MYSQLDB_HOST'),
            port=crawler.settings.get('MYSQLDB_PORT', 3306),
            db  =crawler.settings.get('MYSQLDB_NAME'),
            user=crawler.settings.get('MYSQLDB_USER'),
            password=crawler.settings.get('MYSQLDB_PASSWORD')
        )

    def open_spider(self, spider):
        # 同步连接方式
        self.conn = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            db=self.db,
            charset='utf8mb4'
        )
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):
        try:
            sql = "INSERT INTO books (title, url) VALUES (%s, %s)"
            self.cursor.execute(sql, (item['title'], item['url']))
            self.conn.commit()  # 必须显式提交事务
        except pymysql.Error as e:
            self.conn.rollback()
            spider.logger.error(f"MySQL Error: {e}")
        return item

    def close_spider(self, spider):
        self.conn.close()
